package com.visizen.wisdomclass.server.client;

import com.visizen.wisdomclass.server.model.*;
import com.visizen.wisdomclass.server.model.dto.ExamExt;
import com.visizen.wisdomclass.server.model.dto.ScoreExt;
import com.visizen.wisdomclass.server.model.dto.UserExt;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.Date;
import java.util.List;

public interface WcExamMapper {
    /**
     * 学生获取成绩趋势按年和月份
     *
     * @param classId
     * @param userId
     * @param startDate
     * @param endDate
     * @return
     */
    @Select(" SELECT\n" +
            "  wc_exam.*,\n" +
            "  (SELECT\n" +
            "  score_value\n" +
            "   FROM wc_score\n" +
            "   WHERE exam_id = wc_exam.exam_id AND user_id = #{userId}) userScore,\n" +
            "  (select avg(score_value) from wc_score where exam_id = wc_exam.exam_id) avgScore\n" +
            " FROM wc_exam\n" +
            " WHERE exam_cycle_id IN (SELECT\n" +
            "                          exam_cycle_id\n" +
            "                        FROM wc_exam_cycle\n" +
            "                        WHERE organization_id = #{classId}) and exam_date >= #{startDate} and exam_date < #{endDate}" +
            " order by course_id asc;")
    @Results({
            @Result(property = "examCycle", column = "exam_cycle_id", one = @One(select = "selectExamCycleById")),
            @Result(property = "userScore", column = "userScore"),
            @Result(property = "avgScore", column = "avgScore")
    })
    List<ExamExt> selectStudentScoreTrendByDate(@Param("classId") Long classId,
                                                @Param("userId") Long userId,
                                                @Param("startDate") Date startDate,
                                                @Param("endDate") Date endDate);

    /**
     * 获取班级最新一次平均分和指定用户的分数
     *
     * @param classId
     * @param userId
     * @return
     */
    @Select(" SELECT\n" +
            "  *,\n" +
            "  (SELECT\n" +
            "  score_value\n" +
            "   FROM wc_score\n" +
            "   WHERE user_id = #{userId} AND exam_id = exam.exam_id) userScore,\n" +
            "  (SELECT\n" +
            "  avg(score_value)\n" +
            "   FROM wc_score\n" +
            "   WHERE exam_id = exam.exam_id) avgScore\n" +
            " FROM\n" +
            "  (SELECT\n" +
            "     wc_exam.*\n" +
            "   FROM wc_exam\n" +
            "   WHERE exam_cycle_id IN (SELECT\n" +
            "                             exam_cycle_id\n" +
            "                           FROM wc_exam_cycle\n" +
            "                           WHERE organization_id = #{classId})\n" +
            "   ORDER BY exam_date DESC\n" +
            "  ) exam\n" +
            " GROUP BY course_id;")
    @Results({
            @Result(property = "course", column = "course_id", one = @One(select = "selectCourseById")),
            @Result(property = "userScore", column = "userScore"),
            @Result(property = "avgScore", column = "avgScore")
    })
    List<ExamExt> selectCourseAvgScoreAndStu(@Param("classId") Long classId,
                                             @Param("userId") Long userId);

    /**
     * 查询班级的所有学生信息
     *
     * @param classId
     * @return
     */
    @Select(" SELECT\n" +
            "  *\n" +
            " FROM wc_exam\n" +
            " WHERE exam_cycle_id IN (SELECT\n" +
            "                          exam_cycle_id\n" +
            "                        FROM wc_exam_cycle\n" +
            "                        WHERE organization_id = #{classId})\n" +
            " GROUP BY course_id\n" +
            " ORDER BY course_id ASC;")
    @Results({
            @Result(property = "scores", javaType = List.class, column = "exam_id", many = @Many(select = "selectOneScoresByExamId"))
    })
    List<ExamExt> selectExamsByCourseIdClassId(@Param("classId") Long classId);

    /**
     * 某次考试的成绩
     *
     * @param examId
     * @return
     */
    @Select(" SELECT\n" +
            "  wc_score.*\n" +
            " FROM wc_score\n" +
            " WHERE wc_score.exam_id = #{examId}" +
            " order by score_value desc")
    @Results({
            @Result(property = "user", column = "score_id", one = @One(select = "selectUserExtByScoreId"))
    })
    List<ScoreExt> selectOneScoresByExamId(@Param("examId") Long examId);

    /**
     * 查询成绩所对应的学生
     *
     * @param scoreId
     * @return
     */
    @Select(" SELECT\n" +
            "  wc_user.*, score.score_id\n" +
            " FROM wc_user,(SELECT\n" +
            "                 *\n" +
            "               FROM wc_score\n" +
            "               WHERE score_id = #{scoreId}) score\n" +
            "  where wc_user.user_id = score.user_id")
    @Results({
            @Result(property = "latestRank", column = "score_id", one = @One(select = "selectLastRank")),
            @Result(property = "latestPreRank", column = "score_id", one = @One(select = "selectLastPreRank"))
    })
    UserExt selectUserExtByScoreId(@Param("scoreId") Long scoreId);

    /**
     * 查询学生的上次成绩排名
     *
     * @param scoreId
     * @return
     */
    @Select(" SELECT\n" +
            "  row\n" +
            " FROM (\n" +
            "       SELECT\n" +
            "         @row := @row + 1 AS row,\n" +
            "         score_id,\n" +
            "         score_value\n" +
            "       FROM (\n" +
            "              SELECT\n" +
            "                score_id,\n" +
            "                score_value\n" +
            "              FROM wc_score, (SELECT\n" +
            "                                wc_exam.*\n" +
            "                              FROM wc_exam, (SELECT\n" +
            "                                               *\n" +
            "                                             FROM wc_exam\n" +
            "                                             WHERE exam_id = (SELECT\n" +
            "                                                                exam_id\n" +
            "                                                              FROM wc_score\n" +
            "                                                              WHERE score_id = #{scoreId})) exam\n" +
            "                              WHERE wc_exam.course_id = exam.course_id AND wc_exam.exam_cycle_id = exam.exam_cycle_id\n" +
            "                              ORDER BY exam_date DESC\n" +
            "                              LIMIT 1) curExam\n" +
            "              WHERE wc_score.exam_id = curExam.exam_id\n" +
            "              ORDER BY score_value DESC) ranks, (SELECT\n" +
            "                                                   @row := 0) r\n" +
            "     ) rank\n" +
            " WHERE score_id = #{scoreId};")
    int selectLastRank(@Param("scoreId") Long scoreId);

    /**
     * 查询某学生的上上次成绩排名
     *
     * @param scoreId
     * @return
     */
    @Select(" SELECT\n" +
            "  row\n" +
            " FROM (\n" +
            "       SELECT\n" +
            "         @row := @row + 1 AS row,\n" +
            "         user_id\n" +
            "       FROM (\n" +
            "              SELECT\n" +
            "                wc_score.*\n" +
            "              FROM wc_score, (SELECT\n" +
            "                                wc_exam.*\n" +
            "                              FROM wc_exam, (SELECT\n" +
            "                                               *\n" +
            "                                             FROM wc_exam\n" +
            "                                             WHERE exam_id = (SELECT\n" +
            "                                                                exam_id\n" +
            "                                                              FROM wc_score\n" +
            "                                                              WHERE score_id = #{scoreId})) exam\n" +
            "                              WHERE wc_exam.course_id = exam.course_id AND wc_exam.exam_cycle_id = exam.exam_cycle_id\n" +
            "                              ORDER BY exam_date DESC\n" +
            "                              LIMIT 1,1) curExam\n" +
            "              WHERE wc_score.exam_id = curExam.exam_id\n" +
            "              ORDER BY score_value DESC) ranks, (SELECT\n" +
            "                                                   @row := 0) r\n" +
            "     ) rank\n" +
            " WHERE user_id = (select user_id from wc_score where score_id=#{scoreId});")
    int selectLastPreRank(@Param("scoreId") Long scoreId);

    /**
     * 获取老师角色的所属班级指定时间的成绩趋势
     *
     * @param courseId
     * @param teacherId
     * @param classId
     * @param date
     * @return
     */
    @Select(" SELECT * \n" +
            " FROM wc_exam\n" +
            " WHERE course_id = #{courseId} AND teacher_user_id = #{teacherId} AND\n" +
            "      exam_cycle_id IN (SELECT exam_cycle_id\n" +
            "                        FROM wc_exam_cycle\n" +
            "                        WHERE organization_id = #{classId} AND exam_cycle_date > #{date})\n")
    @Results({
            @Result(property = "course_id", column = "course_id"),
            @Result(property = "exam_id", column = "exam_id"),
            @Result(property = "course", column = "course_id", one = @One(select = "selectCourseById")),
            @Result(property = "avgScore", column = "exam_id", one = @One(select = "selectAvgScore")),
            @Result(property = "minScore", column = "exam_id", one = @One(select = "selectMinScore")),
            @Result(property = "maxScore", column = "exam_id", one = @One(select = "selectMaxScore"))
    })
    List<ExamExt> selectLatestScoreTrend(@Param("courseId") Long courseId,
                                         @Param("teacherId") Long teacherId,
                                         @Param("classId") Long classId,
                                         @Param("date") Date date);

    /**
     * 根据班级ID获取班级每个科目所有考试中最新的一次的成绩信息
     *
     * @param classId
     * @return
     */
    @Select(" select *\n" +
            " from (\n" +
            "  select * from wc_exam\n" +
            "  where exam_cycle_id in (select exam_cycle_id from wc_exam_cycle where organization_id=#{classId})\n" +
            "  order by exam_date desc\n" +
            " ) tmp_exam\n" +
            " group by course_id;")
    @Results({
            @Result(property = "course_id", column = "course_id"),
            @Result(property = "exam_id", column = "exam_id"),
            @Result(property = "examCycle", column = "exam_cycle_id", one = @One(select = "selectExamCycleById")),
            @Result(property = "course", column = "course_id", one = @One(select = "selectCourseById")),
            @Result(property = "avgScore", column = "exam_id", one = @One(select = "selectAvgScore"))
    })
    List<ExamExt> selectLatestCoursesByClassId(@Param("classId") Long classId);

    /**
     * 根据班级ID和指定日期获取得分情况
     *
     * @param classId
     * @param startDate
     * @param endDate
     * @return
     */
    @Select(" SELECT\n" +
            "  *\n" +
            " FROM wc_exam\n" +
            " WHERE exam_cycle_id IN (SELECT\n" +
            "                          exam_cycle_id\n" +
            "                        FROM wc_exam_cycle\n" +
            "                        WHERE organization_id = #{classId})\n" +
            "      AND exam_date >= #{startDate} AND exam_date < #{endDate}\n" +
            " ORDER BY course_id ASC;")
    @Results({
            @Result(property = "exam_id", column = "exam_id"),
            @Result(property = "examCycle", column = "exam_cycle_id", one = @One(select = "selectExamCycleById")),
            @Result(property = "avgScore", column = "exam_id", one = @One(select = "selectAvgScore")),
            @Result(property = "minScore", column = "exam_id", one = @One(select = "selectMinScore")),
            @Result(property = "maxScore", column = "exam_id", one = @One(select = "selectMaxScore"))
    })
    List<ExamExt> selectScoreByDate(@Param("classId") Long classId,
                                    @Param("startDate") Date startDate,
                                    @Param("endDate") Date endDate);

    /**
     * 指定某次考试的平均分
     *
     * @param examId
     * @return
     */
    @Select("select avg(score_value) avgScore from wc_score where exam_id=#{examId};")
    Float selectAvgScore(@Param("examId") Long examId);

    /**
     * 指定某次考试的最高分
     *
     * @param examId
     * @return
     */
    @Select("select max(score_value) avgScore from wc_score where exam_id=#{examId};")
    int selectMaxScore(@Param("examId") Long examId);

    /**
     * 指定某次考试的最低分
     *
     * @param examId
     * @return
     */
    @Select("select min(score_value) avgScore from wc_score where exam_id=#{examId};")
    int selectMinScore(@Param("examId") Long examId);

    @Select("SELECT * FROM wc_course where course_id=#{courseId}")
    WcCourse selectCourseById(@Param("courseId") Long courseId);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @SelectProvider(type = WcExamSqlProvider.class, method = "countByExample")
    int countByExample(WcExamExample example);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @DeleteProvider(type = WcExamSqlProvider.class, method = "deleteByExample")
    int deleteByExample(WcExamExample example);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @Delete({"delete from wc_exam",
            "where exam_id = #{exam_id,jdbcType=BIGINT}"})
    int deleteByPrimaryKey(Long exam_id);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @Insert({
            "insert into wc_exam (exam_id, course_id, ",
            "teacher_user_id, exam_cycle_id, ",
            "exam_date)",
            "values (#{exam_id,jdbcType=BIGINT}, #{course_id,jdbcType=BIGINT}, ",
            "#{teacher_user_id,jdbcType=BIGINT}, #{exam_cycle_id,jdbcType=BIGINT}, ",
            "#{exam_date,jdbcType=DATE})"})
    int insert(WcExam record);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @InsertProvider(type = WcExamSqlProvider.class, method = "insertSelective")
    int insertSelective(WcExam record);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @SelectProvider(type = WcExamSqlProvider.class, method = "selectByExample")
    @Results({
            @Result(column = "exam_id", property = "exam_id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "course_id", property = "course_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "teacher_user_id", property = "teacher_user_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "exam_cycle_id", property = "exam_cycle_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "exam_date", property = "exam_date", jdbcType = JdbcType.DATE)})
    List<WcExam> selectByExample(WcExamExample example);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @Select({"select",
            "exam_id, course_id, teacher_user_id, exam_cycle_id, exam_date",
            "from wc_exam", "where exam_id = #{exam_id,jdbcType=BIGINT}"})
    @Results({
            @Result(column = "exam_id", property = "exam_id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "course_id", property = "course_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "teacher_user_id", property = "teacher_user_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "exam_cycle_id", property = "exam_cycle_id", jdbcType = JdbcType.BIGINT),
            @Result(column = "exam_date", property = "exam_date", jdbcType = JdbcType.DATE)})
    WcExam selectByPrimaryKey(Long exam_id);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @UpdateProvider(type = WcExamSqlProvider.class, method = "updateByExampleSelective")
    int updateByExampleSelective(@Param("record") WcExam record,
                                 @Param("example") WcExamExample example);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @UpdateProvider(type = WcExamSqlProvider.class, method = "updateByExample")
    int updateByExample(@Param("record") WcExam record,
                        @Param("example") WcExamExample example);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @UpdateProvider(type = WcExamSqlProvider.class, method = "updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(WcExam record);

    /**
     * This method was generated by MyBatis Generator. This method corresponds to the database table wc_exam
     *
     * @mbggenerated
     */
    @Update({"update wc_exam",
            "set course_id = #{course_id,jdbcType=BIGINT},",
            "teacher_user_id = #{teacher_user_id,jdbcType=BIGINT},",
            "exam_cycle_id = #{exam_cycle_id,jdbcType=BIGINT},",
            "exam_date = #{exam_date,jdbcType=DATE}",
            "where exam_id = #{exam_id,jdbcType=BIGINT}"})
    int updateByPrimaryKey(WcExam record);

    @Select("SELECT * FROM wc_exam WHERE exam_id=#{examId}")
    @Results({
            @Result(property = "exam_id", column = "exam_id"),
            @Result(property = "exam_cycle_id", column = "exam_cycle_id"),
            @Result(property = "examCycle", column = "exam_cycle_id", one = @One(select = "selectExamCycleById")),
            @Result(property = "scores", javaType = List.class, column = "exam_id", many = @Many(select = "selectScoresByExamId"))
    })
    ExamExt selectById(@Param("examId") Long examId);

    @Select("SELECT * FROM wc_exam_cycle WHERE exam_cycle_id=#{examCycleId}")
    WcExamCycle selectExamCycleById(@Param("examCycleId") Long examCycleId);

    /**
     * 获取指定考试的所有得分信
     *
     * @param examId
     * @return
     */
    @Select("SELECT * FROM wc_score WHERE exam_id=#{examId} ")
    @Results({
            @Result(property = "user_id", column = "user_id"),
            @Result(property = "user", column = "user_id", one = @One(select = "selectUserById"))
    })
    List<ScoreExt> selectScoresByExamId(@Param("examId") Long examId);

    @Select("SELECT * From wc_user WHERE user_id=#{userId}")
    UserExt selectUserById(@Param("userId") Long userId);
}